package esgi.stationratp.dal;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import esgi.stationratp.interfaces.IStation;
import esgi.stationratp.model.Station;

public class StationDAL implements IStation {

	private static final String dbName = "StationRATP";
	private static final int dbVersion = 1;
	private static final String stationTableName = "Satations";
	private static final String ColId = "ID";
	private static final String ColNom = "NAME";
	private static final String ColVille = "TOWN";
	private static final String ColX = "X";
	private static final String ColY = "Y";
	private static final String ColReseau = "RESEAU";

	SQLiteDatabase sqliteDatabase;
	StationDatabase stationSqlLiteDatabase;

	public StationDAL(Context context) {
		stationSqlLiteDatabase = new StationDatabase(context, dbName, null,
				dbVersion);
	}

	// open database
	private void open() {
		sqliteDatabase = stationSqlLiteDatabase.getReadableDatabase();
	}

	// close database
	private void close() {
		sqliteDatabase.close();
	}

	public SQLiteDatabase getSqliteDatabase() {
		return sqliteDatabase;
	}

	public long deleteAll() {
		open();
		long _id = sqliteDatabase.delete(stationTableName, "1", null);
		close();
		return _id;
	}

	public Station cursorToStation(Cursor c) {
		if (c.getCount() == 0)
			return null;
		Station station = new Station();

		station.setId(c.getInt(0));
		station.setNom(c.getString(1));
		station.setVille(c.getString(2));
		station.setX(c.getFloat(3));
		station.setY(c.getFloat(4));
		station.setReseau(c.getString(5));

		return station;
	}

	@Override
	public ArrayList<Station> getAllSation() {
		open();
		ArrayList<Station> listOfStations = new ArrayList<Station>();
		Cursor c = sqliteDatabase.query(stationTableName, new String[] { ColId,
				ColNom, ColVille, ColX, ColY, ColReseau }, null, null, null,
				null, ColNom);

		while (c.moveToNext()) {
			listOfStations.add(cursorToStation(c));
		}
		close();
		return listOfStations;
	}

	@Override
	public Station getStationById(int id) {
		open();
		Station station;
		Cursor c = sqliteDatabase.query(stationTableName, new String[] { ColId,
				ColNom, ColVille, ColX, ColY, ColReseau }, ColId + " = " + id,
				null, null, null, null);
		c.moveToFirst();
		station = cursorToStation(c);
		close();
		return station;
	}

	@Override
	public long addNewStation(Station station) {
		open();
		ContentValues values = new ContentValues();
		values.put(ColId, station.getId());
		values.put(ColNom, station.getNom());
		values.put(ColVille, station.getVille());
		values.put(ColX, station.getX());
		values.put(ColY, station.getY());
		values.put(ColReseau, station.getReseau());
		long id = sqliteDatabase.insert(stationTableName, null, values);
		close();
		return id;
	}

	@Override
	public long updateStation(int id, Station station) {
		open();
		ContentValues values = new ContentValues();
		// values.put(ColId, station.getId());
		values.put(ColNom, station.getNom());
		values.put(ColVille, station.getVille());
		values.put(ColX, station.getX());
		values.put(ColY, station.getY());
		values.put(ColReseau, station.getReseau());
		long _id = sqliteDatabase.update(stationTableName, values, ColId
				+ " = " + id, null);
		close();
		return _id;
	}

	@Override
	public long deleteStation(int id) {
		open();
		long _id = sqliteDatabase.delete(stationTableName, ColId + " = " + id,
				null);
		close();
		return _id;
	}

	@Override
	public long deleteAllStations() {
		open();
		long _id = sqliteDatabase.delete(stationTableName, null, null);
		close();
		return _id;
	}

	@Override
	public ArrayList<Station> getStationsByReseau(String reseau) {
		open();
		ArrayList<Station> listOfStations = new ArrayList<Station>();
		Cursor c = sqliteDatabase.query(stationTableName, new String[] { ColId,
				ColNom, ColVille, ColX, ColY, ColReseau }, ColReseau + " = '"
				+ reseau + "'", null, null, null, ColNom);
		while (c.moveToNext()) {
			listOfStations.add(cursorToStation(c));
		}
		close();
		return listOfStations;
	}

	@Override
	public ArrayList<Station> searchStation(String keyword) {
		open();
		ArrayList<Station> listOfStations = new ArrayList<Station>();
		Cursor c = sqliteDatabase.query(stationTableName, new String[] { ColId,
				ColNom, ColVille, ColX, ColY, ColReseau }, ColNom + " Like '%"
				+ keyword + "%'", null, null, null, ColNom);
		while (c.moveToNext()) {
			listOfStations.add(cursorToStation(c));
		}
		close();
		return listOfStations;
	}
}
